package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Product;
import com.ysd.entity.Productcategory;
import com.ysd.util.DBManager;

public class ProductDaoImpl {
	/**
	 * 根据分类Id查询相应的产品列表
	 * 
	 * @param cId 产品分类Id
	 * @return
	 */
	public List<Product> getProduct(int cId) { // 什么时候有参数呢?一般是看sql语句有没有条件
		List<Product> productList = new ArrayList<Product>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;

		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from product where productcategoryId=" + cId;
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int productId = rs.getInt("productId");
				String productName = rs.getString("productName");
				String productImg = rs.getString("productImg");
				Product product = new Product(productId, productName, productImg);
				productList.add(product);
			}
			return productList;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

	/**
	 * 按条件查询
	 * @param p
	 * @return
	 */
	public List<Product> getProductsByWhere(Product p, int page, int size) {
		List<Product> list = new ArrayList<Product>();

		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;

		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句 // 1=1是永真条件(永远为true)
			String sql = "select p.*,pc.productCategoryName from product p,productcategory pc where 1=1";
			// 非空判定
			if (p.getProductId() != 0) { // 如果产品id不等于空,那么代表要根据产品id查询
				sql += " and productId=" + p.getProductId(); // 拼接sql语句
			}
			if (p.getProductName() != null && !p.getProductName().equals("")) {
				sql += " and productName like '%" + p.getProductName() + "%' ";
			}
			if (p.getFlavor() != null && !p.getFlavor().equals("")) {
				sql += " and flavor='" + p.getFlavor() + "' ";
			}
			if (p.getBeginmarketPrice() != 0) {
				sql += " and marketPrice>" + p.getBeginmarketPrice();
			}
			if (p.getEndmarketPrice() != 0) {
				sql += " and marketPrice<" + p.getEndmarketPrice();
			}
			if (p.getBrand() != null && !p.getBrand().equals("")) {
				sql += " and brand='" + p.getBrand() + "' ";
			}
			if (p.getIsDistribution() != null && !p.getIsDistribution().equals("")) {
				sql += " and isDistribution='" + p.getIsDistribution() + "' ";
			}
			if (p.getIsCashPayment() != null && !p.getIsCashPayment().equals("")) {
				sql += " and isCashPayment='" + p.getIsCashPayment() + "' ";
			}
			if (p.getProductCategoryId() != 0) {
				sql += " and p.productCategoryId=" + p.getProductCategoryId();
			}
			sql += " and p.productCategoryId=pc.productCategoryId limit "+(page-1)*size+","+size;

			System.out.println(sql); // 查看sql语句拼接是否成功

			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int productId = rs.getInt("productId");
				String productName = rs.getString("productName");
				int marketPrice = rs.getInt("marketPrice");
				String brand = rs.getString("brand");
				String isDistribution = rs.getString("isDistribution");
				String isCashPayment = rs.getString("isCashPayment");
				String flavor = rs.getString("flavor");
				String productImg = rs.getString("productImg");
				int productCategoryId = rs.getInt("productCategoryId");
				String productCategoryName = rs.getString("productCategoryName");

				Product product = new Product(productId, productName, marketPrice, 0, 0, brand, isDistribution,
						isCashPayment, flavor, productImg, productCategoryId, productCategoryName);
				list.add(product);
			}
			// 正常执行返回用户集合
			return list;
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}

	}
	
	/**
	 * 按条件查询
	 * @param p
	 * @return
	 */
	public List<Product> getProductsByWheres(Product p) {
		List<Product> list = new ArrayList<Product>();

		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;

		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句 // 1=1是永真条件(永远为true)
			String sql = "select p.*,pc.productCategoryName from product p,productcategory pc where 1=1";
			// 非空判定
			if (p.getProductId() != 0) { // 如果产品id不等于空,那么代表要根据产品id查询
				sql += " and productId=" + p.getProductId(); // 拼接sql语句
			}
			if (p.getProductName() != null && !p.getProductName().equals("")) {
				sql += " and productName like '%" + p.getProductName() + "%' ";
			}
			if (p.getFlavor() != null && !p.getFlavor().equals("")) {
				sql += " and flavor='" + p.getFlavor() + "' ";
			}
			if (p.getBeginmarketPrice() != 0) {
				sql += " and marketPrice>" + p.getBeginmarketPrice();
			}
			if (p.getEndmarketPrice() != 0) {
				sql += " and marketPrice<" + p.getEndmarketPrice();
			}
			if (p.getBrand() != null && !p.getBrand().equals("")) {
				sql += " and brand='" + p.getBrand() + "' ";
			}
			if (p.getIsDistribution() != null && !p.getIsDistribution().equals("")) {
				sql += " and isDistribution='" + p.getIsDistribution() + "' ";
			}
			if (p.getIsCashPayment() != null && !p.getIsCashPayment().equals("")) {
				sql += " and isCashPayment='" + p.getIsCashPayment() + "' ";
			}
			if (p.getProductCategoryId() != 0) {
				sql += " and p.productCategoryId=" + p.getProductCategoryId();
			}
			sql += " and p.productCategoryId=pc.productCategoryId";

			System.out.println(sql); // 查看sql语句拼接是否成功

			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int productId = rs.getInt("productId");
				String productName = rs.getString("productName");
				int marketPrice = rs.getInt("marketPrice");
				String brand = rs.getString("brand");
				String isDistribution = rs.getString("isDistribution");
				String isCashPayment = rs.getString("isCashPayment");
				String flavor = rs.getString("flavor");
				String productImg = rs.getString("productImg");
				int productCategoryId = rs.getInt("productCategoryId");
				String productCategoryName = rs.getString("productCategoryName");

				Product product = new Product(productId, productName, marketPrice, 0, 0, brand, isDistribution,
						isCashPayment, flavor, productImg, productCategoryId, productCategoryName);
				list.add(product);
			}
			// 正常执行返回用户集合
			return list;
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}

	}
	

	/**
	 * 根据产品id删除产品
	 * 
	 * @param cId 要删除的产品id
	 * @return 数据库受影响行数
	 */
	public int deleteProductById(int cId) {
		return DBManager.executeUpdate("delete from product where productId=" + cId);
	}

	/**
	 * 添加产品信息
	 * 
	 * @param p
	 * @return 返回受影响行数
	 */
	public int addProductDetails(Product p) {
		return DBManager
				.executeUpdate("insert into product values(null,'" + p.getProductName() + "'," + p.getMarketPrice()
						+ ",'" + p.getBrand() + "','" + p.getIsDistribution() + "','" + p.getIsCashPayment() + "','"
						+ p.getFlavor() + "','" + p.getProductImg() + "'," + p.getProductCategoryId() + ")");
	}

	/**
	 * 通过产品名称查询产品
	 * 
	 * @param name
	 * @return
	 */
	public List<Product> getProductDetailsByName(String name) {
		List<Product> pList = new ArrayList<Product>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try { // 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from product where productName='" + name + "'";
			// 4.执行sql语句
			rs = st.executeQuery(sql); // execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int productId = rs.getInt("productId");
				String productName = rs.getString("productName");
				Product p = new Product(productId, productName, null);
				pList.add(p);
			}
			return pList; // 正常执行返回用户集合
		} catch (SQLException e) { // 异常
			e.printStackTrace();
			return null; // 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs); // 关闭连接
		}
	}

	// 返回list是因为查询的结果可能是多条
	/**
	 * 根据id查询,返回单个对象是因为查询的结果只有可能是一条
	 * @param id
	 * @return
	 */
	public Product getProductDetailsById(int id) {
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Product p = null;
		try { // 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "SELECT * FROM product where productId=" + id;
			// 4.执行sql语句
			rs = st.executeQuery(sql); // execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int productId = rs.getInt(1);
				String productName = rs.getString(2);
				int marketPrice = rs.getInt(3);
				String brand = rs.getString(4);
				String isDistribution = rs.getString(5);
				String isCashPayment = rs.getString(6);
				String flavor = rs.getString(7);
				String productImg = rs.getString(8);
				int productCategoryId = rs.getInt(9);
				p = new Product(productId, productName, marketPrice, brand, isDistribution, isCashPayment, flavor, productImg, productCategoryId);				
			}
			return p; // 正常执行返回用户集合
		} catch (SQLException e) { // 异常
			e.printStackTrace();
			return null; // 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs); // 关闭连接
		}
	}
	
	/**
	 * 修改产品信息
	 * @param p 
	 * @return 返回受影响行数
	 */
	public int updateProductDetailsById(Product p) {
		String sql = "UPDATE product set productName='"+p.getProductName()+"',marketPrice="+p.getMarketPrice()+",brand='"+p.getBrand()+"',isDistribution='"+p.getIsDistribution()+"',isCashPayment='"+p.getIsCashPayment()+"',flavor='"+p.getFlavor()+"',productImg='"+p.getProductImg()+"',productCategoryId='"+p.getProductCategoryId()+"' where productId=" + p.getProductId();
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	/***
	 *根据id批量删除产品
	 *返回受影响行数 int
	 *
	 *
	 */
	public int delProductByIds(String ids) {
		return DBManager.executeUpdate("delete from product where productId in("+ids+")");
	}
}
